package com.vt.statusmanager;

import java.io.File;
import java.io.IOException;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import com.vt.statusmanager.data.Manager;
import com.vt.statusmanager.data.Managers;
import com.vt.statusmanager.delta.ExelOperation;
import com.vt.statusmanager.weekdateinformer.NumberOfWeek;
import com.vt.statusmanager.xml.XmlOperations;

/**
 * Класс реализующий выгрузку отчетов о деятельности менеджеров по неделям в отделтный файл
 * 
 * @author Vit
 * 
 */
public class ExelExtractor {
	/**
	 * Основное хранилище с менеджерами и их информацией
	 */
	private final Managers managers = new Managers();

	/**
	 * Добавить менеджера
	 * 
	 * @param managerSecondName Фамилия менеджера
	 * @param pathToDoc Путь до эксель документа с данными менеджера
	 */
	public void addManager(String managerSecondName, String pathToDoc) {
		Preconditions.notBlank(managerSecondName, pathToDoc);
		Preconditions.notNull(managerSecondName, pathToDoc);
		managers.addManager(managerSecondName, pathToDoc);
	}

	/**
	 * Вернуть менеджера
	 * 
	 * @param managerSecondName Фамилия менеджера
	 */
	public Manager getManager(String managerSecondName) {
		Preconditions.notBlank(managerSecondName);
		Preconditions.notNull(managerSecondName);
		return managers.getManager(managerSecondName);
	}

	/**
	 * Выполняет выгрузку данных менеджеров с их недельных отчетов в основное хранилище
	 */
	public void extractExel() {
		try {
			for (Manager manager : managers.getManagers()) {
				Workbook workbook = Workbook.getWorkbook(new File(manager.getPathToFile()));
				Sheet sheet = workbook.getSheet(0);
				for (int i = 0; i < sheet.getRows() - 3; i++) {
					// Если менеджер удалит строку не быдет вылетать ошибка при таком коде
					if (sheet.getCell(0, i + 3).getContents().trim().equals("")) {
						System.out.println("Обнаружено пустое поле, переход к следующему менеджеру");
						break;
					}
					manager.setWeekRecord(Integer.parseInt(sheet.getCell(0, i + 3).getContents()), sheet.getCell(1, i + 3).getContents(), sheet.getCell(2, i + 3).getContents(), sheet.getCell(3, i + 3).getContents());
				}
				workbook.close();
			}
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Создание сводного отчета из данных основного хранилища, с указанием года, по которому велись данные очеты
	 * 
	 * @param pathToReport Путь до сводного отчета
	 * @param year Год
	 */
	public void generateReport(String pathToReport, int year) {
		Preconditions.notNull(pathToReport, year);
		Preconditions.notBlank(pathToReport);
		try {
			WritableWorkbook workbook = Workbook.createWorkbook(new File(pathToReport));
			WritableSheet sheet = workbook.createSheet("Сводный отчет", 0);
			// Формат шапки таблицы
			WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
			WritableCellFormat arial10format = new WritableCellFormat(arial10font);
			arial10format.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
			arial10format.setAlignment(Alignment.LEFT);
			arial10format.setVerticalAlignment(VerticalAlignment.TOP);
			arial10format.setWrap(true);
			// Формат основных данных
			WritableFont arial10fontData = new WritableFont(WritableFont.ARIAL, 10);
			WritableCellFormat arial10formatData = new WritableCellFormat(arial10fontData);
			arial10formatData.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
			arial10formatData.setAlignment(Alignment.LEFT);
			arial10formatData.setVerticalAlignment(VerticalAlignment.TOP);
			arial10formatData.setWrap(true);
			// Закрашивающая полоса
			WritableFont arial10fontWeek = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
			WritableCellFormat arial10formatWeek = new WritableCellFormat(arial10fontWeek);
			arial10formatWeek.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
			arial10formatWeek.setBackground(Colour.AQUA);
			arial10formatWeek.setWrap(true);
			sheet.addCell(new Label(0, 0, "План работ менеджера на неделю", arial10format));
			sheet.addCell(new Label(0, 2, "Порядковый № Недели", arial10format));
			sheet.addCell(new Label(1, 2, "ФИО", arial10format));
			sheet.addCell(new Label(2, 2, "Содержание запланированных мероприятий (дел)", arial10format));
			sheet.addCell(new Label(3, 2, "Отметка о выполнении", arial10format));
			sheet.addCell(new Label(4, 2, "Примечание", arial10format));
			sheet.addCell(new Label(5, 2, "Прошлое содержание", arial10format));
			sheet.addCell(new Label(6, 2, "Прошлая отметка", arial10format));
			sheet.addCell(new Label(7, 2, "Прошлое примечание", arial10format));
			sheet.setColumnView(0, 25);
			sheet.setColumnView(1, 17);
			sheet.setColumnView(2, 40);
			sheet.setColumnView(3, 20);
			sheet.setColumnView(4, 30);
			sheet.setColumnView(5, 40);
			sheet.setColumnView(6, 20);
			sheet.setColumnView(7, 30);
			// Получение объекта с менеджерами записанного ранее в специальном файле
			// Managers pastManagers = XmlOperations.loadFromXml("C:\\managerstatus\\SavedRecords.xml");
			Managers pastManagers = ExelOperation.loadFromExel("C:\\managerstatus\\SavedRecords.xls");
			int nextManagerStep = 0;
			boolean onceWeekForMan = true;
			for (Manager manager : managers.getManagers()) {
				int simpleStep = 0;
				for (int i = 0; i < manager.getNumberOfRecords(); i++) {
					// Расположение менеджеров в документе
					sheet.addCell(new Label(1, (4 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, manager.getSecondName(), arial10format));
					sheet.addCell(new Label(2, (4 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, manager.getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getContent(), arial10formatData));
					sheet.addCell(new Label(3, (4 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, manager.getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getResultData(), arial10formatData));
					sheet.addCell(new Label(4, (4 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, manager.getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getComments(), arial10formatData));
					// Сравнение текущих записей с предыдущими
					if (!(manager.getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getContent().equals(pastManagers.getManager(manager.getSecondName()).getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getContent()))) {
						sheet.addCell(new Label(5, (4 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, manager.getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getContent(), arial10formatData));
					} else sheet.addCell(new Label(5, (4 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, "", arial10formatData));
					if (!(manager.getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getResultData().equals(pastManagers.getManager(manager.getSecondName()).getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getResultData()))) {
						sheet.addCell(new Label(6, (4 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, manager.getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getResultData(), arial10formatData));
					} else sheet.addCell(new Label(6, (4 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, "", arial10formatData));
					if (!(manager.getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getComments().equals(pastManagers.getManager(manager.getSecondName()).getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getComments()))) {
						sheet.addCell(new Label(7, (4 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, manager.getWeekRecord(i + manager.getFurstRecord().getNamberWeek()).getComments(), arial10formatData));
					} else sheet.addCell(new Label(7, (4 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, "", arial10formatData));
					// Расположение номеров недель в документе + продолжение цветной линии
					if (onceWeekForMan) {
						sheet.addCell(new Label(0, (3 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, Integer.toString(i + manager.getFurstRecord().getNamberWeek()) + "  " + NumberOfWeek.getInterval(year, i + manager.getFurstRecord().getNamberWeek()), arial10formatWeek));
						sheet.addCell(new Label(1, (3 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, "", arial10formatWeek));
						sheet.addCell(new Label(2, (3 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, "", arial10formatWeek));
						sheet.addCell(new Label(3, (3 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, "", arial10formatWeek));
						sheet.addCell(new Label(4, (3 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, "", arial10formatWeek));
						sheet.addCell(new Label(5, (3 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, "", arial10formatWeek));
						sheet.addCell(new Label(6, (3 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, "", arial10formatWeek));
						sheet.addCell(new Label(7, (3 + (simpleStep * (managers.getManagersCount() + 1))) + nextManagerStep, "", arial10formatWeek));
					}
					simpleStep++;
				}
				onceWeekForMan = false;
				nextManagerStep++;
			}
			workbook.write();
			workbook.close();
			// XmlOperations.saveXml("C:\\managerstatus\\SavedRecords.xml", managers);
			ExelOperation.saveExel("C:\\managerstatus\\SavedRecords.xls", managers);
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
